local dir `c(pwd)'

********************************************************************************
* CONSTRUCT AGGREGATE ANALYST LONG-TERM GROWTH EXPECTATIONS
********************************************************************************
* Data all come from I/B/E/S Unadjusted Summary Statistics files from WRDS 

*-------------------------------------------------------------------------------
* Clean raw files
*-------------------------------------------------------------------------------
* 1. Actuals, Pricing and Ancillary, US File
* EPS, all query variables
use "../Data/IBESACTUAL.dta", clear
rename *, lower

keep if measure == "EPS" 
sort ticker statpers
quietly by ticker statpers: gen dup = cond(_N==1,0,_n)
drop if dup > 0   // tiny number 
save temp1, replace 

* 2. Summary Statistics, US File
* EPS, Long-Term Growth (0), all query variables
use "../Data/IBESLTG.dta", clear
rename *, lower

keep if measure == "EPS" 
keep if curcode == "USD" 
keep if usfirm == 1 
sort ticker statpers
merge 1:1 ticker statpers using temp1
drop _merge dup
save temp1, replace 

* 2. Summary Statistics, US File
* EPS, Fiscal Year 1 (1)/Fiscal Year 2 (2)/Fiscal Year 3 (3)/Fiscal Year 4 (4), all query variables
use "../Data/IBESFY.dta", clear
rename *, lower

keep if measure == "EPS" 
keep if curcode == "USD" 
keep if usfirm == 1 
*drop curcode usfirm oftic measure meanest
keep ticker statpers fpi medest
reshape wide medest, i(ticker statpers) j(fpi) string
sort ticker statpers
merge 1:1 ticker statpers using temp1
drop _merge 
keep if year(statpers)*100+month(statpers)>198408   // 90 percent of shout obs. missing prior to Aug 1984

keep if medest ~= . 
keep if medest1 > 0  
keep if medest1 ~= . 
keep if medest2 ~= . 
keep if shout ~= .    
gen ce = medest1*shout 
gen ce2 = medest2*shout 
gen ce3 = medest3*shout 
gen fy0e = fy0a*shout
gen mtgest0 = medest1/fy0a-1 if fy0a > 0  
gen mtgest = medest2/medest1-1 if medest1 > 0  // don't use logs, medest2 can be negative
gen mtgest2 = medest3/medest2-1 if medest2 > 0
gen mtgest3 = medest4/medest3-1 if medest3 > 0
save temp1, replace

  
*-------------------------------------------------------------------------------
* Average across tickers 
*-------------------------------------------------------------------------------
keep if fy0e > 0 
collapse (mean) mtgest0 [aw=fy0e], by(statpers) 
save temp2, replace 

use temp1, clear 
keep if ce > 0 
collapse (mean) medest mtgest (rawsum) sumce=ce [aw=ce], by(statpers) 
merge 1:1 statpers using temp2
drop _merge
save temp2, replace 

use temp1, clear 
keep if ce2 > 0 
collapse (mean) mtgest2 [aw=ce2], by(statpers) 
merge 1:1 statpers using temp2
drop _merge
save temp2, replace 

use temp1, clear 
keep if ce3 > 0 
collapse (mean) mtgest3 [aw=ce3], by(statpers) 
merge 1:1 statpers using temp2
drop _merge
gen yqdate = qofd(statpers)
format yqdate %tq

*-------------------------------------------------------------------------------
* Aaverage within quarter
*-------------------------------------------------------------------------------
collapse (mean) medest mtgest mtgest2 mtgest3 mtgest0 sumce, by(yqdate) 
save temp, replace 

*-------------------------------------------------------------------------------
* Clean SPF inflation forecasts
*-------------------------------------------------------------------------------
* https://www.philadelphiafed.org/surveys-and-data/real-time-data-research/inflation-forecasts

* "Additional-CPIE10.xlsx"
import excel "../Data/Additional-CPIE10.xlsx", firstrow case(lower) clear
gen infcpi10yr = bluechip
replace infcpi10yr = livingston if missing(bluechip)
replace infcpi10yr = infcpi10yr[_n-1] if missing(infcpi10yr) /*For early periods, assume long-term inflation forecasts stay the same when missing*/

gen yyyy = substr(surveydate, 1, 4)
gen qtr = substr(surveydate, -1, 1)
destring yyyy, replace
destring qtr, replace

keep yyyy qtr infcpi10yr
drop if missing(yyyy)
gen yqdate = yq(yyyy, qtr)
format yqdate %tq
save inftemp, replace

* INFCPI1YR and INFCPI10YR from "inflation.xlsx" 
import excel "../Data/inflation.xlsx", firstrow case(lower) clear

foreach var of varlist inf*{
	destring `var', replace ignore("#N/A")
}

keep year quarter infcpi1yr infcpi10yr
drop if missing(year)
rename year yyyy
rename quarter qtr
gen yqdate = yq(yyyy, qtr)
format yqdate %tq

merge 1:1 yqdate using inftemp, update
drop _merge
save inftemp, replace

*-------------------------------------------------------------------------------
* Merge with inflation forecasts
*-------------------------------------------------------------------------------
merge 1:1 yqdate using temp
drop _merge

drop if yyyy < 1984 | (yyyy == 1984 & qtr <2)

gen mtg0 = mtgest0
gen ltg = medest/100
gen mtg = mtgest
gen mtg2 = mtgest2
gen mtg3 = mtgest3 

save ibesdata, replace 

sort yqdate
gen realltg = (ltg - (infcpi10yr[_n-1]/2 + infcpi1yr[_n-1]/2)/100)/4
gen realmtg = (mtg - infcpi1yr[_n-1]/100)/4 

* Export to Excel
keep yyyy qtr realltg realmtg
drop if missing(realltg) & missing(realmtg)
export excel using "IBES_Forecast", keepcellfmt firstrow(variables) replace

erase temp1.dta
erase temp2.dta
erase temp.dta
erase inftemp.dta




